<?php

namespace app\admin\controller;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Db;
use app\service\OrderNumberGenerator;

class Export extends Common
{
    public function topup()
    {
        $post = input('');
        $order = input('order/d', 1);
        $ordersort = ['t1.addtime desc', 't1.addtime asc'];

        if (isset($post['delAll']) && $post['delAll']) {
            $where = ['t1.id' => ['in', $post['delAll']]];
        } else {
            $where = ['t1.type' => ['neq', 3], 'state' => ['neq', 2]];

            $post['url'] = urldecode(str_replace('/admin/member/topup', '', $post['url']));

            $url = explode('?', $post['url']);

            if (isset($url[0]) && isset($url[1]) && $url[0] != '') {
                $url_1 = explode('/', $url[0]);

                if(in_array('state', $url_1)){
                    $where['t1.state'] = $url_1[sizeof($url_1) - 1];
                }
            }

            if(isset($url[1]) && $url[1]){
                $params = explode('&', $url[1]);
                $url_params = [];

                foreach ($params as $key => $item) {
                    $item = explode('=', $item);

                    $url_params[$item[0]] = $item[1];
                }

                $search_name = $post['name'] ?? '';
                $search_type = $url_params['search'] ?? '';
                if ($search_name && $search_type) {
                    if ($search_type == 'id') {
                        $where['t2.' . $search_type] = $search_name;
                    } else {
                        $where['t2.' . $search_type] = ['like', '%' . $search_name . '%'];
                    }
                }
            }


            $start = $post['start'] ?? '';
            $end = $post['end'] ?? '';
            if ($start && $end) {
                $where['t1.addtime'] = ['between', [date('Y-m-d H:i:s', strtotime($start)), date('Y-m-d H:i:s', strtotime($end))]];
            } else if ($start && !$end) {
                $where['t1.addtime'] = ['egt', date('Y-m-d H:i:s', strtotime($start))];
            } else if (!$start && $end) {
                $where['t1.addtime'] = ['elt', date('Y-m-d H:i:s', strtotime($end))];
            }
        }

        $data = Db::name('topup t1')
            ->field('t1.*,t2.name,t3.id as yqrid')
            ->join('user t2', 't2.id = t1.fk_userid', 'LEFT')
            ->join('user t3', 't3.yqm = t2.yqr', 'LEFT')
            ->where($where)
            ->order($ordersort[$order - 1])
            ->fetchSql(false)
            ->select();

        $datalen = sizeof($data);
        if($datalen == 0){
            echo '无数据';exit;
        }elseif($datalen > 100000){
            echo '单次导出不可超出十万条数据，请合理安排分批导出';exit;
        }

        if ($data) {
            foreach ($data as $key => $item) {
                $data[$key]['buyer_id'] = '';
                if ($item['data']) {
                    $json = json_decode($item['data'], true);
                    if ($json) {
                        if (isset($json['openid']) && $json['openid']) {
                            $data[$key]['buyer_id'] = $json['openid'];
                        } else if (isset($json['buyer_id']) && $json['buyer_id']) {
                            $data[$key]['buyer_id'] = $json['buyer_id'];
                        }
                    }
                }
                if ($item['name'] === null) {
                    $data[$key]['name'] = '已注销';
                }

                if ($item['type'] == 0) {
                    $data[$key]['type_msg'] = '微信';
                } else if ($item['type'] == 1) {
                    $data[$key]['type_msg'] = '支付宝';
                }

                if ($item['state'] == 0) {
                    $data[$key]['state_msg'] = '待付款';
                } else if ($item['state'] == 1) {
                    $data[$key]['state_msg'] = '已付款';
                } else if ($item['state'] == 2) {
                    $data[$key]['state_msg'] = '已失效';
                }
            }

            $this->exportTopup($data);

            $data = ['ret' => '成功'];
        } else {
            $data = ['err' => '失败，未选中数据'];
        }
        return $data;
    }
    public function topupBack()
    {
        $post = input('');
        $order = input('order/d', 1);
        $ordersort = ['t1.addtime desc', 't1.addtime asc'];

        if (isset($post['delAll']) && $post['delAll']) {
            $where = ['t1.id' => ['in', $post['delAll']]];
        } else {
            $where = ['t1.type' => ['neq', 3], 'state' => ['neq', 2]];

            $post['url'] = urldecode(str_replace('/admin/member/topup', '', $post['url']));

            $url = explode('?', $post['url']);

            if (isset($url[0]) && isset($url[1]) && $url[0] != '') {
                $url_1 = explode('/', $url[0]);

                if(in_array('state', $url_1)){
                    $where['t1.state'] = $url_1[sizeof($url_1) - 1];
                }
            }

            if(isset($url[1]) && $url[1]){
                $params = explode('&', $url[1]);
                $url_params = [];

                foreach ($params as $key => $item) {
                    $item = explode('=', $item);

                    $url_params[$item[0]] = $item[1];
                }

                $search_name = $post['name'] ?? '';
                $search_type = $url_params['search'] ?? '';
                if ($search_name && $search_type) {
                    if ($search_type == 'id') {
                        $where['t2.' . $search_type] = $search_name;
                    } else {
                        $where['t2.' . $search_type] = ['like', '%' . $search_name . '%'];
                    }
                }
            }


            $start = $post['start'] ?? '';
            $end = $post['end'] ?? '';
            if ($start && $end) {
                $where['t1.addtime'] = ['between', [date('Y-m-d H:i:s', strtotime($start)), date('Y-m-d H:i:s', strtotime($end))]];
            } else if ($start && !$end) {
                $where['t1.addtime'] = ['egt', date('Y-m-d H:i:s', strtotime($start))];
            } else if (!$start && $end) {
                $where['t1.addtime'] = ['elt', date('Y-m-d H:i:s', strtotime($end))];
            }
        }

        $data = Db::name('topup t1')
            ->field('t1.*,t2.name')
            ->join('user t2', 't2.id = t1.fk_userid', 'LEFT')
            ->where($where)
            ->order($ordersort[$order - 1])
            ->limit(20)
            ->fetchSql(false)
            ->select();

        $datalen = sizeof($data);
        if($datalen == 0){
            echo '无数据';exit;
        }

        if ($data) {
            $text = '';
            foreach ($data as $key => $item) {
                $text .= $item['numbers'] . ',' .  $item['price'] . ',退款,' .  $item['numbers'] . "back\r\n";
            }

            header("Content-type:application/octet-stream");
            header("Accept-Ranges:bytes");
            header("Content-Disposition:attachment;filename=".'退款_'.date("YmdHis").".txt");
            header("Expires:0");
            header("Cache-Control:must-revalidate,post-check=0,pre-check=0 ");
            header("Pragma:public");
            echo $text;exit;

            $data = ['ret' => '成功'];
        } else {
            $data = ['err' => '失败，未选中数据'];
        }
        return $data;
    }

    public function exportTopup($data)
    {
        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '订单号')
            ->setCellValue('C1', '用户ID')
            ->setCellValue('D1', '用户昵称')
            ->setCellValue('E1', '价格')
            ->setCellValue('F1', '金币')
            ->setCellValue('G1', '充值方式')
            ->setCellValue('H1', '充值状态')
            ->setCellValue('I1', '充值时间')
            ->setCellValue('J1', '上级ID')
            ->setCellValue('K1', '支付平台用户唯一标识');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('充值订单');
        $i = 2;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'] . "\t")
                ->setCellValue('B' . $i, $v['numbers'] . "\t")
                ->setCellValue('C' . $i, $v['fk_userid'] . "\t")
                ->setCellValue('D' . $i, $v['name'] . "\t")
                ->setCellValue('E' . $i, $v['price'])
                ->setCellValue('F' . $i, $v['money'])
                ->setCellValue('G' . $i, $v['type_msg'] . "\t")
                ->setCellValue('H' . $i, $v['state_msg'] . "\t")
                ->setCellValue('I' . $i, $v['addtime'] . "\t")
                ->setCellValue('J' . $i, $v['yqrid'] . "\t")
                ->setCellValue('K' . $i, $v['buyer_id'] . "\t");
            $i++;
        }

        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }

    public function income()
    {
        $post = input('');
        $where = [];


        $order = input('order/d', 1);
        $ordersort = ['t1.id desc', 't1.time asc'];

        if (isset($post['delAll']) && $post['delAll']) {
            $where = ['t1.id' => ['in', $post['delAll']]];
        } else {
            // $where = ['t1.data' => 0];

            $post['url'] = urldecode(str_replace('/admin/shouyi/records', '', $post['url']));

            $url = explode('?', $post['url']);

            if (isset($url[0]) && isset($url[1]) && $url[0] != '') {
                $url_1 = explode('/', $url[0]);

                if(in_array('toward', $url_1)){
                    $where['t1.data'] = $url_1[sizeof($url_1) - 1];
                }
            }

            if (isset($url[1])) {
                $params = explode('&', $url[1]);
                $url_params = [];
    
                foreach ($params as $key => $item) {
                    $item = explode('=', $item);
        
                    $url_params[$item[0]] = $item[1];
                }
            }
            
            $search_name = $post['name'] ?? '';
            $search_type = $url_params['search'] ?? '';
            if ($search_name && $search_type) {
                if ($search_type == 'id') {
                    $where['t2.' . $search_type] = $search_name;
                } else {
                    $where['t2.' . $search_type] = ['like', '%' . $search_name . '%'];
                }
            }

            $start = $post['start'] ?? '';
            $end = $post['end'] ?? '';
            if ($start && $end) {
                $where['t1.time'] = ['between', [strtotime($start), strtotime($end)]];
            } else if ($start && !$end) {
                $where['t1.time'] = ['egt', strtotime($start)];
            } else if (!$start && $end) {
                $where['t1.time'] = ['elt', strtotime($end)];
            }
        }

        $data = Db::name('yaoqing_records t1')
            ->field('t1.*,t2.name, t3.name username')
            ->join('user t2', 't1.uid = t2.id')
            ->join('user t3', 't1.touid = t3.id', 'LEFT')
            ->where($where)
            ->order($ordersort[$order - 1])
            ->fetchSql(false)
            ->select();

        $datalen = sizeof($data);
        if($datalen == 0){
            echo '无数据';exit;
        }elseif($datalen > 100000){
            echo '单次导出不可超出十万条数据，请合理安排分批导出';exit;
        }

        if ($data) {
            foreach ($data as $key => $item) {
                if ($item['name'] === null) {
                    $data[$key]['name'] = '已注销';
                }

                if ($item['username'] === null) {
                    $data[$key]['username'] = '已注销';
                }

                $data[$key]['option'] = $item['data'] == 0 ? '+' : '-';

                $data[$key]['time'] = date('Y-m-d H:i:s', $item['time']);
            }

            $this->exportIncome($data);

            $data = ['ret' => '成功'];
        } else {
            $data = ['err' => '失败，未选中数据'];
        }
        return $data;
    }

    public function exportIncome($data)
    {
        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '用户ID')
            ->setCellValue('C1', '用户昵称')
            ->setCellValue('D1', '来源用户ID')
            ->setCellValue('E1', '来源用户昵称')
            ->setCellValue('F1', '描述')
            ->setCellValue('G1', '操作')
            ->setCellValue('H1', '积分')
            ->setCellValue('I1', '账户积分')
            ->setCellValue('J1', '时间');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('收益记录');
        $i = 2;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'] . "\t")
                ->setCellValue('B' . $i, $v['uid'] . "\t")
                ->setCellValue('C' . $i, $v['name'] . "\t")
                ->setCellValue('D' . $i, $v['touid'] . "\t")
                ->setCellValue('E' . $i, $v['username'] . "\t")
                ->setCellValue('F' . $i, $v['content'] . "\t")
                ->setCellValue('G' . $i, $v['option'] . "\t")
                ->setCellValue('H' . $i, $v['money'])
                ->setCellValue('I' . $i, $v['balance'])
                ->setCellValue('J' . $i, $v['time'] . "\t");
            $i++;
        }

        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $spreadsheet->getActiveSheet()->getStyle('G1:G' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }

    public function balance()
    {
        $post = input('');
        $order = input('order/d', 1);
        $ordersort = ['t1.id desc', 't1.time asc'];

        if (isset($post['delAll']) && $post['delAll']) {
            $where = ['t1.id' => ['in', $post['delAll']]];
        } else {
            $where = ['t1.state' => 0];

            $post['url'] = urldecode(str_replace('/admin/member/balance', '', $post['url']));

            $url = explode('?', $post['url']);

            if (isset($url[0]) && isset($url[1]) && $url[0] != '') {
                $url_1 = explode('/', $url[0]);

                if(in_array('types', $url_1)){
                    $where['t1.types'] = $url_1[sizeof($url_1) - 1];
                }
            }

            if(input('type')){
                $where['t1.content'] = ['like', '%发消息扣金币'];
            }

            if(isset($url[1]) && $url[1]){
                $params = explode('&', $url[1]);
                $url_params = [];

                foreach ($params as $key => $item) {
                    $item = explode('=', $item);

                    $url_params[$item[0]] = $item[1];
                }
            }
            $search_name = $post['name'] ?? '';
            $search_type = $url_params['search'] ?? '';
            if ($search_name && $search_type) {
                if ($search_type == 'id') {
                    $where['t2.' . $search_type] = $search_name;
                } else {
                    $where['t2.' . $search_type] = ['like', '%' . $search_name . '%'];
                }
            }

            $start = $post['start'] ?? '';
            $end = $post['end'] ?? '';
            if ($start && $end) {
                $where['t1.time'] = ['between', [strtotime($start), strtotime($end)]];
            } else if ($start && !$end) {
                $where['t1.time'] = ['egt', strtotime($start)];
            } else if (!$start && $end) {
                $where['t1.time'] = ['elt', strtotime($end)];
            }
        }

        $data = Db::name('volley_desc t1')
            ->field('t1.*,t2.name')
            ->join('user t2', 't1.user_id = t2.id')
            ->where($where)
            ->order($ordersort[$order - 1])
            ->fetchSql(false)
            ->select();


        $datalen = sizeof($data);
        if($datalen == 0){
            echo '无数据';exit;
        }elseif($datalen > 100000){
            echo '单次导出不可超出十万条数据，请合理安排分批导出';exit;
        }

        if ($data) {
            foreach ($data as $key => $item) {
                if ($item['name'] === null) {
                    $data[$key]['name'] = '已注销';
                }

                $data[$key]['msg'] = $item['types'] ? '+' : '-';
                $data[$key]['time'] = date('Y-m-d H:i:s', $item['time']);

                preg_match("/.*\((\d*)\)/", $item['content'], $matches);

                if (isset($matches[1])) {
                    $data[$key]['to_user'] = $matches[1];
                } else {
                    $data[$key]['to_user'] = '';
                }
            }
            $touids = Db::name('user')->where(['id'=>['in', array_unique(array_filter(array_column($data,'to_user')))]])->column('reg,authcode','id');
            $userData = Db::name('user_data')->where(['user_id'=>['in', array_filter(array_column($data, 'user_id'))]])->column('topup', 'user_id');
            foreach ($data as $key => $item) {
                if ($item['to_user'] && isset($touids[$item['to_user']])) {
                    if ($touids[$item['to_user']]['reg'] == 2 && $touids[$item['to_user']]['authcode']) {
                        $data[$key]['to_user'] = $touids[$item['to_user']]['authcode'];
                    }
                }

                $data[$key]['topup'] = $userData[$item['user_id']] ?? 0;
            }

            $this->exportBalance($data);

            $data = ['ret' => '成功'];
        } else {
            $data = ['err' => '失败，未选中数据'];
        }
        return $data;
    }

    public function exportBalance($data)
    {
        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '用户ID')
            ->setCellValue('C1', '用户昵称')
            ->setCellValue('D1', '来源用户ID')
            ->setCellValue('E1', '描述')
            ->setCellValue('F1', '状态')
            ->setCellValue('G1', '金额')
            ->setCellValue('H1', '账户金币')
            ->setCellValue('I1', '时间')
            ->setCellValue('J1', '充值金额');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('账户流水');
        $i = 2;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'] . "\t")
                ->setCellValue('B' . $i, $v['user_id'] . "\t")
                ->setCellValue('C' . $i, $v['name'] . "\t")
                ->setCellValue('D' . $i, $v['to_user'] . "\t")
                ->setCellValue('E' . $i, $v['content'] . "\t")
                ->setCellValue('F' . $i, $v['msg'] . "\t")
                ->setCellValue('G' . $i, $v['price'])
                ->setCellValue('H' . $i, $v['balance'])
                ->setCellValue('I' . $i, $v['time'] . "\t")
                ->setCellValue('J' . $i, $v['topup']);
            $i++;
        }

        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }
    
    public function withdraw()
    {
        $post = input('');
        
        if (isset($post['delAll']) && $post['delAll']) {
            $where = ['t1.id' => ['in', $post['delAll']]];
        } else {
            $where = ['channel'=>1];
            
            $post['url'] = urldecode(str_replace('/admin/member/tixian', '', $post['url']));
            
            $url = explode('?', $post['url']);

            if (isset($url[0]) && $url[0] != '') {
                $url_1 = explode('/', $url[0]);
                
                if ($url_1[2] == 3) {
                    $where['t1.state'] = 0;
                } else if ($url_1[2] == 4) {
                    $where['t1.state'] = 1;
                } else if ($url_1[2] == 5) {
                    $where['t1.state'] = 2;
                }
            }

            if (isset($url[1])) {
                $params = explode('&', $url[1]);
                $url_params = [];
                
                foreach ($params as $key => $item) {
                    $item = explode('=', $item);
                    
                    $url_params[$item[0]] = $item[1];
                }
            }
            
            $search_name = $post['name'] ?? '';
            $search_type = $url_params['search'] ?? '';
            if ($search_name && $search_type) {
                if ($search_type == 'id') {
                    $where['t2.' . $search_type] = $search_name;
                } else {
                    $where['t2.' . $search_type] = ['like', '%' . $search_name . '%'];
                }
            }
            
            $start = $post['start'] ?? '';
            $end = $post['end'] ?? '';
            if ($start && $end) {
                $where['t1.create_time'] = ['between', [strtotime($start), strtotime($end)]];
            } else if ($start && !$end) {
                $where['t1.create_time'] = ['egt', strtotime($start)];
            } else if (!$start && $end) {
                $where['t1.create_time'] = ['elt', strtotime($end)];
            }
        }

        $data = Db::name('withdraw t1')
            ->field('t1.*,t2.name')
            ->join('user t2', 't2.id = t1.userid')
            ->where($where)
            ->order('t1.update_time desc, t1.id desc')
            ->select();


        $idcards = Db::name('user_certification')->where(['user_id'=>['in', array_column($data, 'userid')], 'types'=>1])->column('data,data2', 'user_id');
        
        if ($data) {
            foreach ($data as $key => $item) {
                if ($item['name'] === null) {
                    $data[$key]['name'] = '已注销';
                }
                
                $data[$key]['create_time'] = date('Y-m-d H:i:s', $item['create_time']);
                $data[$key]['update_time'] = date('Y-m-d H:i:s', $item['update_time']);
                
                $data[$key]['paymethod'] = $item['paymethod'] ? '支付宝' : '微信';
    
                $datas = json_decode($item['data'], true);
    
                $data[$key]['tixian_name'] = $datas['name'] ?? '';
                $data[$key]['tixian_account'] = $datas['account'] ?? '';
                $data[$key]['tixian_order'] = $datas['order_id'] ?? '';
                
                $data[$key]['reason'] = $item['state'] == 2 ? $item['reason'] : '';
                
                if ($item['state'] == 1) {
                    $data[$key]['state'] = '成功';
                } else if ($item['state'] == 2) {
                    $data[$key]['state'] = '拒绝';
                } else {
                    $data[$key]['state'] = '审核';
                }

                $data[$key]['idcard'] = '';
                $data[$key]['realname'] = '';
                if (isset($idcards[$item['userid']])) {
                    $data[$key]['idcard'] = $idcards[$item['userid']]['data'];
                    $data[$key]['realname'] = $idcards[$item['userid']]['data2'];
                }
            }
            
            $this->exportWithdraw($data);
            
            $data = ['ret' => '成功'];
        } else {
            $data = ['err' => '失败，未选中数据'];
        }
        return $data;
    }
    
    public function exportWithdraw($data)
    {
        $spreadsheet = new Spreadsheet();
        
        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '用户ID')
            ->setCellValue('C1', '用户昵称')
            ->setCellValue('D1', '提现积分')
            ->setCellValue('E1', '手续费')
            ->setCellValue('F1', '现金')
            ->setCellValue('G1', '提现方式')
            ->setCellValue('H1', '姓名')
            ->setCellValue('I1', '认证姓名')
            ->setCellValue('J1', '身份证号')
            ->setCellValue('K1', '账号')
            ->setCellValue('L1', '支付宝单号')
            ->setCellValue('M1', '处理描述')
            ->setCellValue('N1', '进度')
            ->setCellValue('O1', '创建时间')
            ->setCellValue('P1', '更新时间');
        
        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('提现明细');
        $i = 2;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'] . "\t")
                ->setCellValue('B' . $i, $v['userid'] . "\t")
                ->setCellValue('C' . $i, $v['name'] . "\t")
                ->setCellValue('D' . $i, $v['price'])
                ->setCellValue('E' . $i, $v['shouxu'] . "\t")
                ->setCellValue('F' . $i, $v['money'])
                ->setCellValue('G' . $i, $v['paymethod'] . "\t")
                ->setCellValue('H' . $i, $v['tixian_name'] . "\t")
                ->setCellValue('I' . $i, $v['realname'] . "\t")
                ->setCellValue('J' . $i, $v['idcard'] . "\t")
                ->setCellValue('K' . $i, $v['tixian_account'] . "\t")
                ->setCellValue('L' . $i, $v['tixian_order'] . "\t")
                ->setCellValue('M' . $i, $v['reason'] . "\t")
                ->setCellValue('N' . $i, $v['state'] . "\t")
                ->setCellValue('O' . $i, $v['create_time'] . "\t")
                ->setCellValue('P' . $i, $v['update_time'] . "\t");
            $i++;
        }
        
        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);
        
        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');
        
        $writer = new Xlsx($spreadsheet);
        
        $writer->save('php://output');
        
        exit;
    }

    public function WithdrawMerge($paymethod = 1)
    {
        $money = input('money') ? input('money') : 0;
        $order = input('order') ? input('order') : 1;

       $post = input('');
       if (isset($post['delAll']) && $post['delAll']) {
          $optids = implode(',', $post['delAll']);
        }else{
            $optids = false;
        }        

        $where = ['channel'=>1];
        if($money){
            $moneyArea = [[0,99.99],[100,299.99],[300,599.99],[600,999.99],[999,99999999]];
            $where['t1.money'] = ['between', $moneyArea[$money - 1]];
        }
        if ($order == 1) {
            $order = 'desc';
        } else if ($order == 2) {
            $order = 'asc';
        }
        
        $where['t1.paymethod'] = $paymethod;
        $where['t1.zhifubao_export_no'] = null;
        if($optids){
             $data = Db::name('withdraw t1')
            ->field('t1.*,t2.name')
            ->join('user t2', 't2.id = t1.userid')
            ->where(['t1.state'=>1, 't1.money'=>['>',0], 't2.delete_time'=>0])
            ->where($where)
            ->where('t1.id', 'in', $optids)
            ->order('t1.money ' . $order . ', t1.userid asc')
            ->select();
        }else{
             $data = Db::name('withdraw t1')
            ->field('t1.*,t2.name')
            ->join('user t2', 't2.id = t1.userid')
            ->where(['t1.state'=>1, 't1.money'=>['>',0], 't2.delete_time'=>0])
            ->where($where)
            //->where('t1.id', 'in', $optids)
            ->order('t1.money ' . $order . ', t1.userid asc')
            ->select();
        }

       
        
        if (sizeof($data)) {
            $appname = getenv('APP_NAME');
            foreach ($data as $key => $item) {
                if ($item['name'] === null) {
                    $data[$key]['name'] = '已注销';
                }
                
                
                $data[$key]['order_no'] = OrderNumberGenerator::generate(12);
                $data[$key]['userid'] = $item['userid'];
                $datas = json_decode($item['data'], true);
    
                $data[$key]['tixian_name'] = $datas['name'] ?? '';
                $data[$key]['tixian_account'] = $datas['account'] ?? '';
                $data[$key]['tixian_money'] = $item['money'] ?? 0;
                $data[$key]['remark'] = $appname . 'APP用户(' . $item['userid'] . ')佣金';
                
                Db::name('withdraw')->where('id', $item['id'])->update(['zhifubao_export_no' => $data[$key]['order_no']]);
            }
            
            $this->exportWithdrawMerge($data);
            
            $data = '成功';
        } else {
            $data = '失败，没有合并数据';
        }
        echo $data;
    }
    public function exportWithdrawMerge($data)
    {
        $spreadsheet = new Spreadsheet();
        
        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->mergeCells('A1:E1')
            ->setCellValue('A1', '支付宝批量付款文件模板（前面两行请勿删除）')
            ->setCellValue('A2', '序号（必填）')
            ->setCellValue('B2', '收款方支付宝账号（必填）')
            ->setCellValue('C2', '收款方姓名（必填）')
            ->setCellValue('D2', '金额（必填，单位：元）')
            ->setCellValue('E2', '备注（选填）');
        
        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('提现明细');
        $i = 3;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['order_no'] . "\t")
                ->setCellValue('B' . $i, $v['tixian_account'] . "\t")
                ->setCellValue('C' . $i, $v['tixian_name'] . "\t")
                ->setCellValue('D' . $i, $v['tixian_money'])
                ->setCellValue('E' . $i, $v['remark'] . "\t");
            $i++;
        }
        
        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:E1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);
        
        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . str_replace('.', '', microtime(true)) . '.xlsx"');
        
        $writer = new Xlsx($spreadsheet);
        
        $writer->save('php://output');
        
        exit;
    }



    public function WithdrawMergeWechat()
    {
        $money = input('money') ? input('money') : 0;
        $order = input('order') ? input('order') : 1;
        $where = ['channel'=>1];
        if($money){
            $moneyArea = [[0,99.99],[100,299.99],[300,599.99],[600,999.99],[999,99999999]];
            $where['t1.money'] = ['between', $moneyArea[$money - 1]];
        }
        if ($order == 1) {
            $order = 'desc';
        } else if ($order == 2) {
            $order = 'asc';
        }
        $where['t1.paymethod'] = 0;
        $data = Db::name('withdraw_merge t1')
            ->field('t1.*,t2.name')
            ->join('user t2', 't2.id = t1.userid')
            ->where(['t1.state'=>0, 't1.money'=>['>',0], 't2.delete_time'=>0])
            ->where($where)
            ->order('t1.money ' . $order . ', t1.userid asc')
            ->select();
        
        $time = time();
        if (sizeof($data)) {
            $appname = getenv('APP_NAME');
            $sum = 0;
            $list = [];
            foreach ($data as $key => $item) {

                if ($item['name'] === null) {
                    $witem['name'] = '已注销';
                }
                
                $witem['userid'] = $item['userid'];
                $datas = json_decode($item['data'], true);
    
                $witem['tixian_name'] = $datas['name'] ?? '';
                $witem['tixian_account'] = $datas['account'] ?? '';
                $witem['remark'] = $item['userid'] . ' ' . $appname . 'APP用户佣金';

                $maxValue = 200;
                $rateMax = $item['money'] / $maxValue;
                if ($rateMax > 1) {
                    $value = $item['money'];
                    $result = [];

                    while ($value > 0) {
                        if ($value <= $maxValue) {
                            $result[] = $value;
                            break;
                        }
                        $result[] = $maxValue;
                        $value -= $maxValue;
                    }
                    foreach ($result as $k => $v) {
                        $witem['order_sn'] = $item['userid'] . 'sp' . $k . 'wt' . $time;
                        $witem['tixian_money'] = $v ? floatval($v) : 0;
                        $sum += $witem['tixian_money'];

                        $list[] = $witem;
                    }
                } else {
                    $witem['order_sn'] = $item['userid'] . 'wt' . $time;
                    $witem['tixian_money'] = $item['money'] ? floatval($item['money']) : 0;
                    $sum += $witem['tixian_money'];

                    $list[] = $witem;
                }

            }

            $make = [
                'wt' . $time,
                '那蓝之恋APP用户佣金',
                getenv('WECHAT_APPID2'),
                $sum,
                sizeof($list),
                date('Y-m-d') . '微信转账'
            ];
            
            $this->exportWithdrawMergeWechat($list, $make);
            
            $data = '成功';
        } else {
            $data = '失败，没有合并数据';
        }
        echo $data;
    }


    public function exportWithdrawMergeWechat($data, $make)
    {
        $spreadsheet = new Spreadsheet();
        
        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->mergeCells('A1:F1')
            ->mergeCells('B2:F2')
            ->mergeCells('B3:F3')
            ->mergeCells('B4:F4')
            ->mergeCells('B5:F5')
            ->mergeCells('B6:F6')
            ->mergeCells('B7:F7')
            ->mergeCells('A8:F8')
            ->mergeCells('A9:F9')
            ->setCellValue('A1', '微信支付批量转账到零钱模版（勿删）')
            ->setCellValue('A2', '商家批次单号（必填）')
            ->setCellValue('B2', $make[0])
            ->setCellValue('A3', '批次名称（必填）')
            ->setCellValue('B3', $make[1])
            ->setCellValue('A4', '转账appid（必填）')
            ->setCellValue('B4', $make[2])
            ->setCellValue('A5', '转账总金额（必填，单位：元）')
            ->setCellValue('B5', $make[3])
            ->setCellValue('A6', '转账总笔数（必填）')
            ->setCellValue('B6', $make[4])
            ->setCellValue('A7', '批次备注（必填）')
            ->setCellValue('B7', $make[5])
            ->setCellValue('A9', '转账明细（勿删）')
            ->setCellValue('A10', '商家明细单号（必填）')
            ->setCellValue('B10', '收款用户openid（必填）')
            ->setCellValue('C10', '收款用户姓名（选填）')
            ->setCellValue('D10', '收款用户身份证（选填）')
            ->setCellValue('E10', '转账金额（必填，单位：元）')
            ->setCellValue('F10', '转账备注（必填）');
        
        // Rename worksheet
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(150, 'pt');
        $spreadsheet->getActiveSheet()->setTitle('Sheet1');
        $i = 11;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['order_sn'] . "\t")
                ->setCellValue('B' . $i, $v['tixian_account'] . "\t")
                ->setCellValue('C' . $i, $v['tixian_name'] . "\t")
                ->setCellValue('D' . $i, "\t")
                ->setCellValue('E' . $i, $v['tixian_money'])
                ->setCellValue('F' . $i, $v['remark'] . "\t");
            $i++;
        }
        
        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('A9:F9')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);
        
        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');
        
        $writer = new Xlsx($spreadsheet);
        
        $writer->save('php://output');
        
        exit;
    }


    public function lianmaiorder()
    {
        $whereOr = $where = ['t1.status' => 1];

        $whereOr['t1.types'] = $where['t1.types'] = 2;

        $order = input('order', 'id');

        $search_name = input('param.name');
        $search_type = input('param.search');
        if ($search_name && $search_type) {
            if (stripos($search_name, ',') && $search_type == 'user_id') {
                $search_name = explode(',', $search_name);
                $where['t1.user_id']  = $search_name[0];
                $where['t1.data'] = ['like', '%'.$search_name[1].'%'];
                $whereOr['t1.user_id']  = $search_name[1];
                $whereOr['t1.data'] = ['like', '%'.$search_name[0].'%'];
            }else{
                $whereOr['t1.' . $search_type] = $where['t1.' . $search_type] = $search_name;
            }
        }
    
        $start = input('start');
        $end = input('end');
        if ($start && $end) {
            $whereOr['t1.create_time'] = $where['t1.create_time'] = ['between', [strtotime($start), strtotime($end)]];
        } else if ($start && !$end) {
            $whereOr['t1.create_time'] = $where['t1.create_time'] = ['egt', strtotime($start)];
        } else if (!$start && $end) {
            $whereOr['t1.create_time'] = $where['t1.create_time'] = ['elt', strtotime($end)];
        }

        $data = Db::name('order_common t1')
            ->field('t1.*, t2.name,t2.phone')
            ->join('user t2', 't2.id = t1.user_id')
            ->where($where)->whereOr(function ($query) use ($whereOr) {
                $query->where($whereOr);
            })
            ->order('t1.'.$order.' desc')
            ->select();

        foreach ($data as $key => $value) {
            $tmpdata = json_decode($value['data'],true);
            $data[$key]['touserid'] = $tmpdata['another_id'] ?? 0;
            $data[$key]['type'] = $tmpdata['types'] == 1 ? '语音' : '视频';
            $data[$key]['create_time'] = date('Y-m-d H:i:s', $value['create_time']);
            $data[$key]['update_time'] = date('Y-m-d H:i:s', $value['update_time']);
        }
        $this->exportLianmaiOrder($data);
    }
    public function exportLianmaiOrder($data)
    {
        $spreadsheet = new Spreadsheet();
        
        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '订单id')
            ->setCellValue('B1', '连麦类型')
            ->setCellValue('C1', '扣费方')
            ->setCellValue('D1', '订单号')
            ->setCellValue('E1', '收益方')
            ->setCellValue('F1', '金额')
            ->setCellValue('G1', '创建时间')
            ->setCellValue('H1', '更新时间');
        
        // Rename worksheet
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(80, 'pt');
        $spreadsheet->getActiveSheet()->setTitle('连麦订单数据');
        $i = 2;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'])
                ->setCellValue('B' . $i, $v['type'])
                ->setCellValue('C' . $i, $v['user_id'])
                ->setCellValue('D' . $i, $v['number'])
                ->setCellValue('E' . $i, $v['touserid'])
                ->setCellValue('F' . $i, $v['money'])
                ->setCellValue('G' . $i, $v['create_time'])
                ->setCellValue('H' . $i, $v['update_time']);
            $i++;
        }
        
        
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);
        
        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');
        
        $writer = new Xlsx($spreadsheet);
        
        $writer->save('php://output');
        
        exit;
    }


    public function privatechat()
    {
        $where = [];
        $whereor = [];

        $type = input('type', 2);
        $types = input('types');

        $sys_fate = input('sys_fate/d', 0);

        if($type == 1){
            $where['addtime'] = ['egt',date('Y-m-d')." 00:00:00"];
        }else if($type == 2){

        }else if($type == 3){
        }else if($type == 4){
            $where['content'] = ['EXP', Db::raw('not like "您围观的Flag%" and content not like "恭喜！本次转盘抽奖你抽中了%" and content not like "您发布的合集%"')];
        }else if($type == 5){
        }

        $search_name=input('name');
        $search_type=input('search');

        if($search_name && $search_type !== null){
            if (stripos($search_name, ',') && $search_type == 'userid') {
                $search_name = explode(',', $search_name);
                $where['userid']  = $search_name[0];
                $where['touserid']  = $search_name[1];
                $whereor['touserid']  = $search_name[0];
                $whereor['userid']  = $search_name[1];
                if($types){
                    $where['type']  = $types;
                    $whereor['type']  = $types;
                }
            } else if($search_type == 'userid') {
                $where['userid']  = $search_name;
                $whereor['touserid']  = $search_name;
                if($types){
                    $where['type']  = $types;
                    $whereor['type']  = $types;
                }
            } else {
                // $where['type'] = $whereor['type'] = 0;
                $where[$search_type] = $whereor[$search_type] = ['like', "%$search_name%"];
                if($types){
                    $where['type']  = $types;
                }
            }
        } else {
            if($type == 3){
                $ids = Db::name('user')->where(['role' => 1])->column('id');
                if ($ids) {
                    $where['userid']  = ['in', $ids];
                    $whereor['touserid']  = ['in', $ids];

                    $this->assign('ids',$ids);
                } else {
                    $where['userid']  = -1;
                    $this->assign('ids',[]);
                }
            }

            if($types){
                $where['type']  = $types;
            }
        }

        $start = input('start');
        $end = input('end');

        if ($start && $end) {
            $where['addtime'] = $whereor['addtime'] = ['between', [$start, $end]];
            $start_id = _day_first_id($start);
            $end_id = _day_first_id(date('Y-m-d', strtotime($start))==date('Y-m-d', strtotime($end))?date('Y-m-d', strtotime($end)+86400):$end, $start_id);
            if ($end_id) {
                $where['id'] = $whereor['id'] = ['between', [$start_id, $end_id]];
            }
        } else if ($start && !$end) {
            $where['addtime'] = $whereor['addtime'] = ['egt', $start];
            $start_id = _day_first_id($start);
            if ($start_id) {
                $where['id'] = $whereor['id'] = ['egt', $start_id];
            }
        } else if (!$start && $end) {
            $where['addtime'] = $whereor['addtime'] = ['elt', $end];
            $end_id = _day_first_id($end);
            if ($end_id) {
                $where['id'] = $whereor['id'] = ['egt', $end_id];
            }
        }

        if (sizeof($whereor) == 1 && isset($whereor['id'])) {
            $whereor = [];
        }

        if ($sys_fate) {
            $where['front_id'] = '';
            $where['type'] = 0;
            $where['is_fate'] = $sys_fate == 1 ? 0 :1;
            $kefu = Db::name('config_system')->where(['title'=>'id'])->value('content');
            if (!isset($where['userid'])) {
                $where['userid'] = ['<>', $kefu];
                if (!isset($where['touserid'])) {
                    $where['touserid'] = ['<>', $kefu];
                }
            } else if (!isset($where['touserid'])) {
                $where['touserid'] = ['<>', $kefu];
            }
            if (!empty($whereor)) {
                $whereor['front_id'] = '';
                $whereor['type'] = 0;
                $whereor['is_fate'] = $where['is_fate'];
                if (!isset($whereor['userid'])) {
                    $whereor['userid'] = ['<>', $kefu];
                    if (!isset($whereor['touserid'])) {
                        $whereor['touserid'] = ['<>', $kefu];
                    }
                } else if (!isset($whereor['touserid'])) {
                    $whereor['touserid'] = ['<>', $kefu];
                }
            }
        }
        // echo "<pre>";
        // var_dump($where);exit;

        if ($type == 2) {
            if(is_array($search_name) || $search_type != 'userid') {

                $data = Db::name('privatechat')->where($where)->whereOr(function ($query) use ($whereor) {
                    $query->where($whereor);
                })->order('id desc')->select();
            } else {

                $data = Db::name('privatechat')->where($where)->order('id desc')->select();
            }
        } elseif($type == 4) {
            // 男用户与客服的记录
            // 避免全表扫描
            $kfid = Db::name('config_system')->where(['title'=>'id'])->cache(60)->value('content');
            $chats = Db::name('privatechat')->where(['userid|touserid'=>$kfid])->field('id,userid,touserid')->select();
            $chatids = array_column($chats, 'id');
            $uidsa = array_column($chats, 'userid');
            $uidsb = array_column($chats, 'touserid');
            $uids = array_unique(array_merge($uidsa, $uidsb));
            $uids = Db::name('user')->where(['sex'=>'男', 'id'=>['in', $uids]])->cache(60)->column('id');
            $uids = implode(',',$uids);
            $where['id'] = ['in', $chatids];
            $where['userid'] = $kfid;
            $where['touserid']  = ['in', $uids];
            $whereor['id'] = ['in', $chatids];
            $whereor['userid']  = ['in', $uids];
            $whereor['touserid'] = $kfid;
            $data = Db::name('privatechat')->where($where)->whereOr(function ($query) use ($whereor) {
                $query->where($whereor);
            })->order('id desc')->select();
        } elseif($type == -1) {
            $data = Db::name('privatechat')->field('*, max(id) id')->where($where)->whereOr(function ($query) use ($whereor) {
                $query->where($whereor);
            })->order('id desc')->select();
        }
        if (sizeof($data) > 100000) {
            exit('超出10万条数据，请分批导出');
        }

        $this->exportChat($data);

    }

    public function exportChat($data)
    {
        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '类型')
            ->setCellValue('C1', '单边消息')
            ->setCellValue('D1', '发送者')
            ->setCellValue('E1', '接收者')
            ->setCellValue('F1', '内容')
            ->setCellValue('G1', '状态')
            ->setCellValue('H1', '是否客服')
            ->setCellValue('I1', '金币')
            ->setCellValue('J1', '积分')
            ->setCellValue('K1', '是否结算')
            ->setCellValue('L1', '是否已读')
            ->setCellValue('M1', '发送时间')
            ->setCellValue('N1', '是否打招呼')
            ->setCellValue('O1', '订单ID')
            ->setCellValue('P1', '发送方用户质量')
            ->setCellValue('Q1', '通话时长')
            ->setCellValue('R1', '前端ID')
            ->setCellValue('S1', '发送方性别');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('充值订单');
        $i = 2;

        $type = ['提醒','提醒','提示','文字','图片','语音','视频','文件','红包','位置','现金红包','礼物','语音通话','视频通话','商品','回复','真心话发起','真心话回应','话匣子发起','话匣子结果','话匣子骰子','商品礼物','商品代付','征婚消息','通用窗口消息','盲盒纸条消息'];

        $status = ['正常','发送方删除','接收方删除','撤回','双方删除'];
        $qualitys = ['未检出','低端','一般','优质','超优质'];

        $users = Db::name('user')->where(['id'=>['in',array_column($data, 'userid')]])->column('quality,sex', 'id');

        foreach ($data as $v) {
            $second = 0;
            if ($v['type'] == 9 || $v['type'] == 10) {
                $lianmaiData = json_decode($v['data'], true);
                if (isset($lianmaiData['end'])) {
                    $second = $lianmaiData['end'] - $lianmaiData['start'];
                }
            }
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'] . "\t")
                ->setCellValue('B' . $i, $type[$v['type'] + 3] . "\t")
                ->setCellValue('C' . $i, $v['single'] ? '是' : '否' . "\t")
                ->setCellValue('D' . $i, $v['userid'] . "\t")
                ->setCellValue('E' . $i, $v['touserid'] . "\t")
                ->setCellValue('F' . $i, $v['content'] . "\t")
                ->setCellValue('G' . $i, $status[$v['status']] . "\t")
                ->setCellValue('H' . $i, $v['k'] ? '是' : '否' . "\t")
                ->setCellValue('I' . $i, $v['money'])
                ->setCellValue('J' . $i, $v['score'])
                ->setCellValue('K' . $i, $v['finish'] ? '是' : '否' . "\t")
                ->setCellValue('L' . $i, $v['isRead'] ? '是' : '否' . "\t")
                ->setCellValue('M' . $i, $v['addtime'] . "\t")
                ->setCellValue('N' . $i, !$v['is_fate']&&!$v['front_id']?'系统':($v['is_fate'] ? '是' : '否') . "\t")
                ->setCellValue('O' . $i, $v['order_id'] . "\t")
                ->setCellValue('P' . $i, isset($users[$v['userid']]) ? ($qualitys[$users[$v['userid']]['quality']] ?? '') : '')
                ->setCellValue('Q' . $i, $second)
                ->setCellValue('R' . $i, $v['front_id'])
                ->setCellValue('S' . $i, isset($users[$v['userid']]) ? ($users[$v['userid']]['sex'] ?? '') : '');
            $i++;
        }

        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="privatechat_' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }

    public function walletFrozen()
    {
        $post = input('');
        $where = [];


        $order = input('order/d', 1);
        $ordersort = ['t1.id desc', 't1.time asc'];

        if (isset($post['delAll']) && $post['delAll']) {
            $where = ['t1.id' => ['in', $post['delAll']]];
        } else {

            $post['url'] = urldecode(str_replace('/admin/wallet_frozen/records', '', $post['url']));

            $url = explode('?', $post['url']);

            if (isset($url[0]) && isset($url[1]) && $url[0] != '') {
                $url_1 = explode('/', $url[0]);

                if(in_array('toward', $url_1)){
                    $where['t1.data'] = $url_1[sizeof($url_1) - 1];
                }
            }
            if (isset($url[1]) && $url[1]) {
                $params = explode('&', $url[1]);
                $url_params = [];
    
                foreach ($params as $key => $item) {
                    $item = explode('=', $item);
        
                    $url_params[$item[0]] = $item[1];
                }
            }
            
            $search_name = $post['name'] ?? '';
            $search_type = $url_params['search'] ?? '';
            if ($search_name && $search_type) {
                if ($search_type == 'id') {
                    $where['t2.' . $search_type] = $search_name;
                } else {
                    $where['t2.' . $search_type] = ['like', '%' . $search_name . '%'];
                }
            }

            $start = $post['start'] ?? '';
            $end = $post['end'] ?? '';
            if ($start && $end) {
                $where['t1.time'] = ['between', [strtotime($start), strtotime($end)]];
            } else if ($start && !$end) {
                $where['t1.time'] = ['egt', strtotime($start)];
            } else if (!$start && $end) {
                $where['t1.time'] = ['elt', strtotime($end)];
            }
        }

        $data = Db::name('wallet_frozen_records t1')
            ->field('t1.*,t2.name')
            ->join('user t2', 't1.user_id = t2.id')
            ->where($where)
            ->order($ordersort[$order - 1])
            ->fetchSql(false)
            ->select();

        $datalen = sizeof($data);
        if($datalen == 0){
            echo '无数据';exit;
        }elseif($datalen > 100000){
            echo '单次导出不可超出十万条数据，请合理安排分批导出';exit;
        }

        if ($data) {
            foreach ($data as $key => $item) {
                if ($item['name'] === null) {
                    $data[$key]['name'] = '已注销';
                }

                $data[$key]['option'] = $item['type'] == 0 ? '冻结' : '解冻';

                $data[$key]['time'] = date('Y-m-d H:i:s', $item['time']);
            }
            $this->exportwalletFrozen($data);

            $data = ['ret' => '成功'];
        } else {
            $data = ['err' => '失败，未选中数据'];
        }
        return $data;
    }

    public function exportwalletFrozen($data)
    {
        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '用户ID')
            ->setCellValue('B1', '用户昵称')
            ->setCellValue('C1', '积分')
            ->setCellValue('D1', '操作')
            ->setCellValue('E1', '操作说明')
            ->setCellValue('F1', '操作时间');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('冻结积分记录');
        $i = 2;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['user_id'] . "\t")
                ->setCellValue('B' . $i, $v['name'] . "\t")
                ->setCellValue('C' . $i, $v['value'])
                ->setCellValue('D' . $i, $v['option'] . "\t")
                ->setCellValue('E' . $i, $v['remark'] . "\t")
                ->setCellValue('F' . $i, $v['time'] . "\t");
            $i++;
        }
        $sheet = $spreadsheet->getActiveSheet();
        foreach ($sheet->getColumnIterator() as $column) {
           $sheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
        }
        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $spreadsheet->getActiveSheet()->getStyle('G1:G' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }

    public function exportPhone($data=[])
    {
        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '用户ID')
            ->setCellValue('B1', '手机号');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('用户');
        $i = 2;
        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'])
                ->setCellValue('B' . $i, $v['phone']);
            $i++;
        }
        $sheet = $spreadsheet->getActiveSheet();
        foreach ($sheet->getColumnIterator() as $column) {
           $sheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
        }

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }

    public function exportMalePhone($date='2023-07-01')
    {
        $users = Db::name('user')
                    ->alias('u')
                    ->join('user_data d','u.id=d.user_id and d.topup<5000', 'left')
                    ->join('user_repeat_check r', 'u.id=r.user_id and r.account_count<2', 'left')
                    ->where(['u.phone'=>['<>', ''], 'u.delete_time'=>0, 'u.reg'=>1, 'u.sex'=>'男', 'u.last_login_datetime'=>['<', $date], 'u.last_login_time'=>['<',strtotime($date)]])
                    ->field('u.id,u.phone')
                    ->order('u.id desc')
                    ->select();
        $this->exportPhone($users);
    }

    public function exportFemalePhone($date='2023-07-01')
    {
        $users = Db::name('user')
                    ->alias('u')
                    ->join('user_data d','u.id=d.user_id and d.income<100000', 'left')
                    ->join('user_repeat_check r', 'u.id=r.user_id and r.account_count<3', 'left')
                    ->where(['u.phone'=>['<>', ''], 'u.delete_time'=>0, 'u.reg'=>1, 'u.sex'=>'女', 'u.last_login_datetime'=>['<', $date], 'u.last_login_time'=>['<',strtotime($date)]])
                    ->field('u.id,u.phone')
                    ->order('u.id desc')
                    ->select();
        $this->exportPhone($users);
    }

    public function exportUserData231024()
    {
        $users = Db::name('user')->where(['reg'=>1])->whereTime('last_login_datetime', '-3 month')->field('id,phone,sex,city,shouyi,register_datetime')->select();
        $userData = Db::name('user_data')->where(['user_id'=>['in',array_column($users,'id')]])->column('consume','user_id');
        $userRepeatCheck = Db::name('user_repeat_check')->where(['user_id'=>['in',array_column($users,'id')]])->column('last_login_app_version,os','user_id');

        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '用户ID')
            ->setCellValue('B1', '手机号')
            ->setCellValue('C1', '性别')
            ->setCellValue('D1', '城市')
            ->setCellValue('E1', '消费金币')
            ->setCellValue('F1', '获得积分')
            ->setCellValue('G1', '注册时间')
            ->setCellValue('H1', 'app版本')
            ->setCellValue('I1', '手机设备信息');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('用户');
        $i = 2;
        foreach ($users as $v) {
            $consume = $userData[$v['id']] ?? 0;
            $app_version = $userRepeatCheck[$v['id']]['last_login_app_version'] ?? '旧版未记录';
            $phone_info = $userRepeatCheck[$v['id']]['os'] ?? '旧版未记录';
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'])
                ->setCellValue('B' . $i, $v['phone'])
                ->setCellValue('C' . $i, $v['sex'])
                ->setCellValue('D' . $i, $v['city'])
                ->setCellValue('E' . $i, $consume)
                ->setCellValue('F' . $i, $v['shouyi'])
                ->setCellValue('G' . $i, $v['register_datetime'])
                ->setCellValue('H' . $i, $app_version)
                ->setCellValue('I' . $i, $phone_info);
            $i++;
        }
        $sheet = $spreadsheet->getActiveSheet();
        foreach ($sheet->getColumnIterator() as $column) {
           $sheet->getColumnDimension($column->getColumnIndex())->setAutoSize(true);
        }

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }

    public function CallbackLianmai()
    {
        $data = Db::name('privatechat')
            ->where(['type'=>10,'data'=>['like','%触发鉴黄警报%']])
            ->whereTime('addtime', '-30 day')
            ->order('id asc')
            ->select();
        $newNihaoDb = Db::connect('mysql://test2:test2@101.35.1.208:3306/test_hunlian211227#utf8');
        $users = $newNihaoDb->name('user')->where(['id'=>['in', array_column($data, 'userid')]])->column('id,name,sex,age','id');
        $exports = [];
        $type = ['语音','视频'];
        foreach ($data as $key => $value) {
            $json = json_decode($value['data'], true);
            // if (isset($json['end']) && $json['end'] - $json['start'] < 20) {
                $value['second'] = isset($json['end']) ? $json['end'] - $json['start'] : 0;
                $value['content'] = "[".$type[$value['type'] - 9]."通话] ".$value['second']."秒 异常挂断原因".$json['hpr'];
                $value['user_sex'] = $users[$value['userid']]['sex'] ?? '';
                $exports[] = $value;
            // }
        }
        // var_dump($exports);exit;
        if (sizeof($exports)) {
            $this->exportCallbackLianmai($exports);
        }
    }

    public function exportCallbackLianmai($data)
    {
        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'ID')
            ->setCellValue('B1', '类型')
            ->setCellValue('C1', '发送者')
            ->setCellValue('D1', '发送者性别')
            ->setCellValue('E1', '接收者')
            ->setCellValue('F1', '内容')
            ->setCellValue('G1', '连麦秒数')
            ->setCellValue('H1', '金币')
            ->setCellValue('I1', '时间')
            ->setCellValue('J1', '订单ID');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('涉黄通话记录');
        $i = 2;

        $type = ['提醒','提醒','提示','文字','图片','语音','视频','文件','红包','位置','现金红包','礼物','语音通话','视频通话','商品','回复','真心话发起','真心话回应','话匣子发起','话匣子结果','话匣子骰子','商品礼物','商品代付'];

        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['id'] . "\t")
                ->setCellValue('B' . $i, $type[$v['type'] + 3] . "\t")
                ->setCellValue('C' . $i, $v['userid'] . "\t")
                ->setCellValue('D' . $i, $v['user_sex'] . "\t")
                ->setCellValue('E' . $i, $v['touserid'] . "\t")
                ->setCellValue('F' . $i, $v['content'] . "\t")
                ->setCellValue('G' . $i, $v['second'])
                ->setCellValue('H' . $i, $v['money'])
                ->setCellValue('I' . $i, date('Y-m-d H:i:s', strtotime($v['addtime'])) . "\t")
                ->setCellValue('J' . $i, $v['order_id'] . "\t");
            $i++;
        }

        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="privatechat_' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }


    public function CallbackLianmai2()
    {
        $data = Db::name('privatechat')
            ->where(['type'=>10,'start'=>['>',0]])
            ->whereTime('addtime', ['2024-05-20','2024-06-03'])
            ->order('id asc,addtime asc')
            ->page(3)
            ->limit(20000)
            ->select();
        $newNihaoDb = Db::connect('mysql://test2:test2@101.35.1.208:3306/test_hunlian211227#utf8');
        $users = $newNihaoDb->name('user')->where(['id'=>['in', array_unique(array_column($data, 'userid'))]])->cache(3600)->column('id,name,sex,age','id');
        $exports = [];
        $type = ['语音','视频'];
        foreach ($data as $key => $value) {
            $json = json_decode($value['data'], true);
            $value['second'] =  $value['end'] - $value['start'];
            $value['content'] = "[".$type[$value['type'] - 9]."通话] ".$value['second']."秒".(isset($json['hpr']) && $json['hpr']?' 异常挂断原因'.$json['hpr']:'');
            $value['user_sex'] = $users[$value['userid']]['sex'] ?? '';
            $exports[] = $value;
        }
        // var_dump($exports);exit;
        if (sizeof($exports)) {
            $this->exportCallbackLianmai($exports);
        }
    }


    public function CallbackLianmai2refreshTime()
    {
        $cachekey = 'CallbackLianmai2refreshTime';
        $lastid = cache($cachekey)?:0;
        $data = Db::name('privatechat')
            ->where(['id'=>['>',$lastid],'type'=>10,'start'=>0])
            ->order('id asc')
            ->limit(1000)
            ->select();
            echo sizeof($data);
        $updates = [];
        foreach ($data as $key => $value) {
            $lastid = $value['id'];
            $json = json_decode($value['data'], true);
            if (isset($json['end']) && isset($json['start']) && $json['end'] - $json['start'] > 0) {
                $updates[] = ['id'=>$value['id'], 'start'=>$json['start'], 'end'=>$json['end']];
            }
        }
        cache($cachekey, $lastid, 86400);
        (new \app\api\model\Privatechat)->saveAll($updates);
        header("refresh:0;");
    }


    public function exportTongjiChat()
    {
        $data = Db::name('tongji_user_data')->where(['date'=>['>=', '2024-01-01']])->select();

        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')
            ->setCellValue('B1', '聊天记录总数量')
            ->setCellValue('C1', '女可发消息对数')
            ->setCellValue('D1', '有聊天的女用户数')
            ->setCellValue('E1', '有聊天的男用户数')
            ->setCellValue('F1', '获得积分账号数量')
            ->setCellValue('G1', '消费金币账号数量')
            ->setCellValue('H1', '总充值')
            ->setCellValue('I1', '充值账号数量')
            ->setCellValue('J1', '充值上限数量');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('每日聊天数据统计');
        $i = 2;

        foreach ($data as $v) {
            $chat = json_decode($v['chat'], true);
            $topup = json_decode($v['topup'], true);
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['date'])
                ->setCellValue('B' . $i, $chat['count'])
                ->setCellValue('C' . $i, $chat['chat_list_count_en'])
                ->setCellValue('D' . $i, $chat['usersexcount']['female'])
                ->setCellValue('E' . $i, $chat['usersexcount']['male'])
                ->setCellValue('F' . $i, $chat['usersexcount']['female_s'])
                ->setCellValue('G' . $i, $chat['usersexcount']['male_s'])
                ->setCellValue('H' . $i, $topup['count_price'])
                ->setCellValue('I' . $i, $topup['count_account'])
                ->setCellValue('J' . $i, $topup['count_limit']);
            $i++;
        }

        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="privatechat_' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }


    public function blackList()
    {
        ini_set('memory_limit', '2024M');
        $blacks = Db::name('blacklist')->select();
        $users = Db::name('user')->column('sex,last_login_datetime', 'id');
        $user_intimate = Db::name('user_intimate')->column('intimate', 'couple_a');

        foreach ($blacks as &$value) {
            $value['sex'] = $users[$value['user_id']]['sex'] ?? '';
            $value['last_login_datetime'] = $users[$value['user_id']]['last_login_datetime'] ?? '';

            $couple_a = $value['user_id'] < $value['to_user'] ? $value['user_id'] . ',' . $value['to_user'] : $value['to_user'] . ',' . $value['user_id'];
            $value['intimate'] = $user_intimate[$couple_a] ?? '';
        }

        // echo "<pre>";
        // print_r($blacks);
        $this->exportBlackList($blacks);
    }


    public function exportBlackList($data)
    {
        $spreadsheet = new Spreadsheet();

        // Add title
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', '主动方ID')
            ->setCellValue('B1', '对方ID')
            ->setCellValue('C1', '主动方性别')
            ->setCellValue('D1', '亲密度')
            ->setCellValue('E1', '拉黑时间')
            ->setCellValue('F1', '主动方最后在线时间');

        // Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('每日聊天数据统计');
        $i = 2;

        foreach ($data as $v) {
            // Add data
            $spreadsheet->getActiveSheet()
                ->setCellValue('A' . $i, $v['user_id'])
                ->setCellValue('B' . $i, $v['to_user'])
                ->setCellValue('C' . $i, $v['sex'])
                ->setCellValue('D' . $i, $v['intimate'])
                ->setCellValue('E' . $i, $v['addtime'])
                ->setCellValue('F' . $i, $v['last_login_datetime']);
            $i++;
        }

        // Set alignment
        $spreadsheet->getActiveSheet()->getStyle('A1:F' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C2:C' . $i)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);

        ob_clean();
        header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
        header('content-type:application/octet-stream');
        header('Cache-Control: max-age=0');
        header("Content-Type:application/force-download");
        header("Content-Type:application/download");
        header('Content-Disposition: attachment;filename="privatechat_' . time() . microtime(true) . '.xlsx"');

        $writer = new Xlsx($spreadsheet);

        $writer->save('php://output');

        exit;
    }
}
